import requests
import pandas as pd
import re
import copy

header={'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/109.0.0.0 Safari/537.36 SLBrowser/9.0.3.5211 SLBChan/109':'aNQReC9bwyzuM4etyUYIXSXhbzL7m2uL'}
path_data=pd.read_excel(r'C:\Users\86191\Desktop\资料PPT\大三学期\智能优化算法\湖大各院坐标.xlsx')
head=path_data['纬度']
tail=path_data['经度']
position=path_data['学院名称']

for i in range(len(position)):
    for j in range(len(position)):
        url = 'https://api.map.baidu.com/routematrix/v2/walking?output=json&origins={0},{1}&destinations={2},{3}&ak=aNQReC9bwyzuM4etyUYIXSXhbzL7m2uL'.format(head[i], tail[i], head[j], tail[j])
        html=requests.get(url,headers=header,timeout=10).text
        html=re.split('[:",}]',html)
        print(html)
        if i==0 and j==0:
            dict_data={'position1':[position[i]],'position2':[position[j]],'distance':[html[30]]}
            data=pd.DataFrame(dict_data,columns=['position1','position2','distance'])
        else:
            dict_data=pd.DataFrame({'position1':[position[i]],'position2':[position[j]],'distance':[html[30]]})
            data=data._append(dict_data,ignore_index=True)
data1=copy.deepcopy(data)
for i in range(len(data)):
    distance_str = data['distance'][i]
    if '公里' in distance_str:
        # 移除“公里”并转换为米（乘以1000）
        data['distance'][i] = float(distance_str.strip('公里')) * 1000
    elif '米' in distance_str:
        # 直接移除“米”
        data['distance'][i] = float(distance_str.strip('米'))
    else:
        # 如果没有单位，默认直接转换为浮点数
        data['distance'][i] = float(distance_str)
writer=pd.ExcelWriter(r'C:\Users\86191\Desktop\111.xlsx',engine='xlsxwriter')
data.to_excel(writer,sheet_name='数据')
writer._save()
writer.close()
